# 学员信息在线管理
import pymysql
import easygui as g


# 定义学生信息数据库操作类
class Stu:
    def __init__(self):  # 初始化连接数据库
        self.db = pymysql.connect(
            host='localhost', user='root', password='', db='studb', charset='utf8')
#-------------查询信息方法--------------------------

    def find_msg(self, sql):
        cursor = self.db.cursor()
        try:
            cursor.execute(sql)
            alllist = cursor.fetchall()
            return alllist
        except Exception as err:
            print('条件错误,原因:', err)
#-------------------删除方法------------------------

    def del_msg(self, sql):
        cursor = self.db.cursor()
        try:
            cursor.execute(sql)
            self.db.commit()
            print('成功删除%d条记录!' % cursor.rowcount)
        except Exception as err:
            self.db.rollback()
            print('条件错误,原因:', err)
#---------------插入信息方法-------------------------

    def insert_msg(self, sql):
        cursor = self.db.cursor()
        try:
            cursor.execute(sql)
            self.db.commit()
            print('成功增加%d条记录!' % cursor.rowcount)
        except Exception as err:
            self.db.rollback()
            print('条件错误,原因:', err)

    def __del__(self):
        self.db.close()

#-------------------学员信息输出界面----------------------------
def showStu(astr,sql):
    '''
    学生信息的输出函数
    '''
    stulist = []
    alist = []
    db = Stu()
    stulist = db.find_msg(sql)
    try:
        if len(stulist) == 0:
            astr = "========== 没有学员信息可以输出！============="
            g.choicebox(msg=astr, title=astr, choices=alist)
            return
        a = "|{0:^5}| {1:^10}| {2:^4}| {3:^5}|{4:^13}|".format(
            "学号", "姓名", "年龄", "性别", "班级")
        alist.append(a)
        for i in stulist:
            b = "|{0:>05}| {1:^10}| {2:^7}| {3:^7}| {4:^10}|".format(
                i[0], i[1], i[2], i[3], i[4])
            alist.append(b)
    except Exception:
        pass
    key = g.choicebox(msg=astr, title=astr, choices=alist)
    print(int(key[5:6]))
    return int(key[5:6])
#---------添加学员信息界面---------------------
def showAdd():
    msg = "学员信息添加"
    title = "账号中心"
    fieldNames = ["*姓名", "*年名", "*性别", "*班级"]
    fieldValues = []
    stu = []
    fieldValues = g.multenterbox(msg, title, fieldNames)

    while 1:
        if fieldValues == None:
            break
        errmsg = ""
        for i in range(len(fieldNames)):
            option = fieldNames[i].strip()
            if fieldValues[i].strip() == "" and option[0] == "*":
                errmsg += ('【%s】为必填项。\n\n' % fieldNames[i])
        if errmsg == "":
            break
        fieldValues = g.multenterbox(errmsg, title, fieldNames, fieldValues)
    for i in range(len(fieldValues)):
        if i == 1:
            c = int(fieldValues[i])
            stu.append(c)
        else:
            stu.append(fieldValues[i])
    return stu


while True:
    # 输出初始界面
    choice = ('1.查看学员信息','2.添加学员信息','3.删除学员信息','4.退出系统')
    key = g.indexbox(msg='学员管理系统',title='学员管理系统',choices=choice)
    print(key)
    if key == 0:
        try:
            sql = 'select * from stumsg'
            msg = '学员信息浏览'
            showStu(msg,sql)
        except Exception:
            pass
    elif key == 1:
        try:
            print("=" * 12, "学员信息添加", "=" * 14)
            db = Stu()
            stu = tuple(showAdd())
            print(stu)
    #------------插入信息---------------------
            sql = "insert into stumsg(name,age,sex,class) values('%s','%d','%s','%s')" % (
                stu)
            db.insert_msg(sql)
            msg = '学员信息浏览'
            showStu(msg,'select * from stumsg')
        except Exception:
            pass
    elif key == 2:
        try:
            print("=" * 12, "学员信息删除", "=" * 14)
            db = Stu()
            msg = '学员信息删除'
            sid = showStu(msg,'select * from stumsg')
    #-------------------删除信息-------------------
            sql = "delete from stumsg where id=%d" % (sid)
            db.del_msg(sql)
            showStu(msg,'select * from stumsg')
        except Exception:
            pass
    elif key == 3 or key == None:
        print("=" * 12, "再见", "=" * 14)
        break

